/*
 * 创建日期 2009-11-27
 *
 * 成都天和软件公司
 * 电话：028-85425861
 * 传真：028-85425861-8008
 * 邮编：610041 
 * 版权所有
 */
package  org.fantasy.common.db.sqlserver;

import java.io.ByteArrayInputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.Predicate;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;

import  org.fantasy.common.db.AbstractQuery;
import  org.fantasy.common.db.bean.Cell;
import  org.fantasy.common.db.bean.ColumnBean;
import  org.fantasy.common.db.bean.ReportRow;
import  org.fantasy.common.db.bean.Row;
import  org.fantasy.common.db.util.EntityUtil;
import  org.fantasy.common.db.util.ResultSetRow;
import  org.fantasy.common.db.util.SqlUtil;
import  org.fantasy.common.exception.DBException;

/**
 *  SqlServer查询、修改、删除、新增
 * @author: 王文成
 * @version: 1.0
 * @since 2009-11-27
 */
public class SqlServerQuery extends AbstractQuery {

	private static final String FORMAT_LIST = "FORMAT_LIST";

	private static final String FORMAT_REPORT = "FORMAT_REPORT";

	private static final String FORMAT_XML = "FORMAT_XML";
	
	private static final String META_DATA = "META_DATA";

	private static Log log = LogFactory.getLog(SqlServerQuery.class);

	@SuppressWarnings("all")
	public int count() throws DBException {
		String countSql = SqlServerUtils.getCountSql(sql, countType);
		String sql = prepare(countSql);
		List<Row> rows = (List<Row>) executeQuery(sql, FORMAT_LIST);
		Cell cell = rows.get(0).getCell(SqlServerUtils.COUNT);
		return cell.getInt();
	}

	public List<? extends Object> list(Class cls) throws DBException {
		List<Row> rows = list();
		List<Object> objList = new ArrayList<Object>();
		for (Row row : rows) {
			Object obj = convertRow(row, cls);
			objList.add(obj);
		}
		return objList;
	}

	@SuppressWarnings("all")
	private Object convertRow(Row row, Class cls) throws DBException {
		try {
			Object obj = cls.newInstance();
			Set<Map.Entry<Object, Cell>> s = (Set<Map.Entry<Object, Cell>>) row.entrySet();
			for (Map.Entry<Object, Cell> e : s) {
				String property = e.getKey().toString();
				Object value = e.getValue().getValue();
				boolean b = EntityUtil.setObjectProperty(obj, property, value);
			}
			return obj;
		} catch (Exception e) {
			log.debug(e);
			throw new DBException(e);
		}
	}

	@SuppressWarnings("all")
	public Row firstRow() throws DBException {
		String sql = prepare(this.sql);
		String qrySql = SqlServerUtils.getTopRowsSql(sql,1);
		List<Row> rows = (List<Row>) executeQuery(qrySql, FORMAT_LIST);
		if (!rows.isEmpty())
			return rows.get(0);
		return null;
	}

	@SuppressWarnings("all")
	public List<Row> list() throws DBException {
		String qrySql = prepare(sql);
		if (isPaged()) {
			return (List<Row>) executeQueryPage(qrySql, FORMAT_LIST);
		} else {
			return (List<Row>) executeQuery(qrySql, FORMAT_LIST);
		}
	}

	@SuppressWarnings("all")
	public List<ReportRow> report() throws DBException {
		String qrySql = prepare(sql);
		if (isPaged()) {
			return (List<ReportRow>) executeQueryPage(qrySql, FORMAT_LIST);
		} else {
			return (List<ReportRow>) executeQuery(qrySql, FORMAT_LIST);
		}
	}

	@SuppressWarnings("all")
	public Document xml() throws DBException {
		String qrySql = prepare(sql);
		if (isPaged()) {
			return (Document) executeQueryPage(qrySql, FORMAT_XML);
		} else {
			return (Document) executeQuery(qrySql, FORMAT_XML);
		}
	}

	/**
	 * 执行查询
	 * 
	 * @param qrySql
	 * @return
	 * @throws DBException
	 */
	private Object executeQuery(String sql, String type) throws DBException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setQueryTimeout(options.getTimeOut());
			log.debug("Set query time out:" + options.getTimeOut());
			log.info(SqlServerUtils.getRuntimeSql(sql, values));
			for (int i = 0; i < values.size(); i++) {
				if (values.get(i) instanceof Integer)
					stmt.setInt(i + 1, ((Integer) values.get(i)).intValue());
				else if (values.get(i) instanceof Long)
					stmt.setLong(i + 1, ((Long) values.get(i)).longValue());
				else if (values.get(i) instanceof String)
					stmt.setString(i + 1, values.get(i).toString());
				else if (values.get(i) instanceof java.sql.Date)
					stmt.setDate(i + 1, (java.sql.Date) values.get(i));
				else if (values.get(i) instanceof java.sql.Timestamp)
					stmt.setTimestamp(i + 1, (java.sql.Timestamp) values.get(i));
				else if (values.get(i) instanceof java.util.Date)
					stmt.setDate(i + 1, new java.sql.Date(((java.util.Date) values.get(i)).getTime()));
				else if (values.get(i) instanceof Double)
					stmt.setDouble(i + 1, ((Double) values.get(i)).doubleValue());
				else if (values.get(i) instanceof Float)
					stmt.setFloat(i + 1, ((Float) values.get(i)).floatValue());
				else
					stmt.setObject(i + 1, values.get(i));
			}
			rs = stmt.executeQuery();
			if (FORMAT_REPORT.equals(type))
				return new ResultSetRow(rs).getRows();
			else if (FORMAT_LIST.equals(type))
				return new ResultSetRow(rs).getReport();
			else if (FORMAT_XML.equals(type))
				return new ResultSetRow(rs).getXml();
			else if (META_DATA.equals(type))
				return new ResultSetRow(rs).getMetaData();
			else
				throw new DBException("无效的数据类型:[" + type + "]");
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex);
			throw new DBException(ex.getMessage(), SqlServerUtils.getRuntimeSql(sql, values));
		} finally {
			SqlServerUtils.closeAll(conn, stmt, rs);
		}
	}

	/**
	 * 执行查询
	 * 
	 * @param qrySql
	 * @return
	 * @throws DBException
	 */
	private Object executeQueryPage(String sql, String type) throws DBException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			stmt.setQueryTimeout(options.getTimeOut());
			log.debug("Set query time out:" + options.getTimeOut());
			log.info(SqlServerUtils.getRuntimeSql(sql, values));
			for (int i = 0; i < values.size(); i++) {
				if (values.get(i) instanceof Integer)
					stmt.setInt(i + 1, ((Integer) values.get(i)).intValue());
				else if (values.get(i) instanceof Long)
					stmt.setLong(i + 1, ((Long) values.get(i)).longValue());
				else if (values.get(i) instanceof String)
					stmt.setString(i + 1, values.get(i).toString());
				else if (values.get(i) instanceof java.sql.Date)
					stmt.setDate(i + 1, (java.sql.Date) values.get(i));
				else if (values.get(i) instanceof java.sql.Timestamp)
					stmt.setTimestamp(i + 1, (java.sql.Timestamp) values.get(i));
				else if (values.get(i) instanceof java.util.Date)
					stmt.setDate(i + 1, new java.sql.Date(((java.util.Date) values.get(i)).getTime()));
				else if (values.get(i) instanceof Double)
					stmt.setDouble(i + 1, ((Double) values.get(i)).doubleValue());
				else if (values.get(i) instanceof Float)
					stmt.setFloat(i + 1, ((Float) values.get(i)).floatValue());
				else
					stmt.setObject(i + 1, values.get(i));
			}
			rs = stmt.executeQuery();
			// 定位行
			rs.absolute(pageStart-1);
			if (FORMAT_REPORT.equals(type))
				return new ResultSetRow(rs, pageSize).getRows();
			else if (FORMAT_LIST.equals(type))
				return new ResultSetRow(rs, pageSize).getReport();
			else if (FORMAT_XML.equals(type))
				return new ResultSetRow(rs, pageSize).getXml();
			else
				throw new DBException("无效的数据类型:[" + type + "]");
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex);
			throw new DBException(ex.getMessage(), SqlServerUtils.getRuntimeSql(sql, values));
		} finally {
			SqlServerUtils.closeAll(conn, stmt, rs);
		}
	}

	public int update() throws DBException {
		String sql = prepare(this.sql);
		return executeUpdate(sql);
	}

	/**
	 * DML
	 * 
	 * @param sql
	 * @return
	 * @throws DBException
	 */
	private int executeUpdate(String sql) throws DBException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(sql);
			log.debug(SqlServerUtils.getRuntimeSql(sql, values));
			for (int i = 0; i < values.size(); i++) {
				if (values.get(i) instanceof Integer)
					stmt.setInt(i + 1, ((Integer) values.get(i)).intValue());
				else if (values.get(i) instanceof Long)
					stmt.setLong(i + 1, ((Long) values.get(i)).longValue());
				else if (values.get(i) instanceof String) {
					String s = (String) values.get(i);
					if (s.getBytes().length < 4000)
						stmt.setString(i + 1, values.get(i).toString());
					else {
						if (s.getBytes().length > 4000)
							stmt.setCharacterStream(i + 1, new InputStreamReader(new ByteArrayInputStream((new String(s.getBytes(), 0, 4000)).getBytes())),
									4000);
						else
							stmt.setCharacterStream(i + 1, new InputStreamReader(new ByteArrayInputStream(s.getBytes())), s.length());
					}
				} else if (values.get(i) instanceof java.sql.Date)
					stmt.setDate(i + 1, (java.sql.Date) values.get(i));
				else if (values.get(i) instanceof java.sql.Timestamp)
					stmt.setTimestamp(i + 1, (java.sql.Timestamp) values.get(i));
				else if (values.get(i) instanceof java.util.Date)
					stmt.setDate(i + 1, new java.sql.Date(((java.util.Date) values.get(i)).getTime()));
				else if (values.get(i) instanceof Double)
					stmt.setDouble(i + 1, ((Double) values.get(i)).doubleValue());
				else if (values.get(i) instanceof Float)
					stmt.setFloat(i + 1, ((Float) values.get(i)).floatValue());
				else if (values.get(i) instanceof BigDecimal)
					stmt.setBigDecimal(i + 1, (BigDecimal) values.get(i));
				else
					stmt.setObject(i + 1, values.get(i));
			}
			int count = stmt.executeUpdate();
			conn.commit();
			return count;
		} catch (SQLException ex) {
			log.error(ex);
			throw new DBException(ex.getMessage(), SqlServerUtils.getRuntimeSql(sql, values));
		} finally {
			SqlServerUtils.closeAll(conn, stmt, null);
		}
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<ColumnBean> getColumns()throws DBException {
		String sql = prepare(this.sql);
		String qrySql = SqlServerUtils.getTopRowsSql(sql,0);
		List<ColumnBean> cols = (List<ColumnBean>) executeQuery(qrySql, META_DATA);
		return cols;
	}

	/**
	 * 取得运行时SQL
	 * 
	 * @return
	 */
	public String getRuntimeSql() {
		String sql = prepare(this.sql);
		return SqlServerUtils.getRuntimeSql(sql, values);
	}

	public static void main(String[] args) {
		String sql = "select to_char('24H:MI:SS') from cust";
		List<String> params = SqlUtil.getParameterList(sql);
		List<Object> paramValues = new ArrayList<Object>();
		Map paramMap = new HashMap();
		if (!paramMap.isEmpty()) {
			// 过滤掉"MI","SS"
			CollectionUtils.filter(params, new Predicate() {
				public boolean evaluate(Object object) {
					String o = (String) object;
					return !("MI".equalsIgnoreCase(o) || "SS".equalsIgnoreCase(o));
				}
			});
			for (String param : params) {
				paramValues.add(paramMap.get(param));
				sql = StringUtils.replaceOnce(sql, ":" + param, "?");
			}
		}
		System.out.println(sql);
	}
}
